The sum of operator sum the FIELDNAME across all records in a related table match the specified selection criteria. The result can appear as a list item in the detail area of a report or as a statistic in the summary area at the end of a report.

There's an important difference between the conditional statistical operator sum and the relational statistical operator sum of. sum sums up the FIELDNAME  of records that satisfy a specified condition among the records being processed. sum of sums the FIELDNAME queried  of matching records related to the records being processed by the script.

TABLENAME

You can use Count of directly on a table without a predefined Relationship. Just remember that if there is a Relationship defined that doesn't have an alternative relationship name, this relationship will be named the same and the table and be used. 

RELATIONSHIP

If you use a relationship (alternative name) then the count of will be automatically restricted by the relational restriction. Read under TABLENAME for functionality when using Relationships without alternative relational name.

You can define an ad hoc named relationship directly in the Count Of function. If you do this you will possibly achieve two things. 1) You will insure against DataEase using a pre-defined relationship with the same name as the table. 2) You can re-use it again in the same script i.e. on a Sum Of etc.

With the WITH statement you define the relational restriction of the function. Ex. MyCustomerNR=CustomerNR and MyDate>current date.

Name of the Data Column/Field that is being summed up ex. InvoiceLineSum, NumberComplaints, InvoiceTotal

Example 1

In a field derivation.Simplest type. We have two tables. ThisTable and MyCustomers. We have no relationships defined.

sum of MyCustomers TotalInvoiced

This will return the sum of what I have invoiced all my customers.

Example 2

We have two tables.CustomerType and MyCustomers. We have a relationship between them that connect CustomerType and MyCustomers on customer type.There is no Unique Alternative Name for the Relationship. I have 300 records in MyCustomer and 45 of type Good Credit. As they all buy a fixed service product that cost $10 i will have invoiced them $450.

My active record in CustomerType is the Good Credit record.

sum of MyCustomers TotalInvoiced

This will return the number $450 as I am now using the relationship instead of the table.

sum of MyCustomers named "AllRecords" 

This will return the number $3000 as I have now defined a unique relationship with no restriction and I will get the total invoiced across the table..

Example 3

sum of MyCustomers named "NewRel" with RegistrationDate=current date TotalInvoiced

This will return the total invoiced today

Example 4

for MEMBERS ;

list records

LAST NAME in order ;

sum of RESERVATIONS TOTAL DUE .

end

This script tells DataEase: (1) Process all the MEMBERS records and list each member's LAST NAME in alphabetical order, (2) for each MEMBERS record processed, find all the related records in the RESERVATIONS table (those that have the same MEMBERID), and (3) list the sum of the TOTAL DUE field for the set of RESERVATIONS records that match the current MEMBERS record.

The output from this script might look as follows:

Last Name

Sum of Reservations

Total Due

Adams

$3000.00

Albert

$4760.00

Anders

$4420.00

Andersen

$2100.00

Anderson

$4320.00

Archer...

$4796.00...

If you want to include the sum of this whole group of reservations, change the fourth line of the query to read:

 sum of RESERVATIONS TOTAL DUE : item sum .

Note: There's an important difference between the statistical operator sum and the relational statistical operator sum of. sum returns the total of the values in the specified field among the records being processed. sum of returns the total of the values in the specified field among the records related to the records being processed.

sum of

Type

Relational Statistical Operator

Purpose

The sum of operator adds the values in a specified field in all matching records in a related table. The result can appear as a list item in the detail area of a report or as a statistic in the summary area at the end of each group or at the end of the report.

Syntax

sum of TABLENAME|RELATIONSHIP

 [named "UNIQUE RELATIONSHIP NAME" ]

 [with ( selection criteria) ] FIELDNAME ; | .

Returns

A numeric value.

Example

for MEMBERS ;

list records

LAST NAME in order ;

sum of RESERVATIONS TOTAL DUE .

end

This script tells DataEase: (1) Process all the MEMBERS records and list each member's LAST NAME in alphabetical order, (2) for each MEMBERS record processed, find all the related records in the RESERVATIONS table (those that have the same MEMBERID), and (3) list the sum of the TOTAL DUE field for the set of RESERVATIONS records that match the current MEMBERS record.

The output from this script might look as follows:

If you want to include the sum of this whole group of reservations, change the fourth line of the query to read:

 sum of RESERVATIONS TOTAL DUE : item sum .

Note: There's an important difference between the statistical operator sum and the relational statistical operator sum of. sum returns the total of the values in the specified field among the records being processed. sum of returns the total of the values in the specified field among the records related to the records being processed.